﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;

namespace codefile
{
    public struct UserList
    {
        public string fID;
        public string fUsername;
        public string fPassword;
        public string fNameofEmployees;
        public string fLevelsOfAccess;
    }
    public struct upadteQTY
    {
        public string Qty;
    }

    public struct ProductList
    {
        public string fID;
        public string fProductName;
        public string fProductQty;
        public string fProductPrice;
    }

    public class Data
    {
        OdbcConnection oc = new OdbcConnection();
        OdbcDataAdapter oda = new OdbcDataAdapter();
        public DataTable DTbl = new DataTable();

        public Data() { }

        public void connect()
        {
            oc.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)}"
                                    + ";DBQ=D://ArleneBarandGrill.accdb"
                                    + ";PWD=";
            oc.Open();
        }

        public void querry(string sql)
        {
            DTbl = new DataTable();
            oda.SelectCommand = new OdbcCommand(sql, oc);
            DTbl.Clear();
            DTbl.Reset();
            oda.Fill(DTbl);
        }

        public void addproduct(ProductList prod)
        {
            DataRow DR = DTbl.NewRow();
            DR["fID"] = prod.fID;
            DR["fProductName"] = prod.fProductName;
            DR["fProductQty"] = prod.fProductQty;
            DR["fProductPrice"] = prod.fProductPrice;

            DTbl.Rows.Add(DR);

            OdbcCommandBuilder OCB = new OdbcCommandBuilder(oda);
            oda.Update(DTbl);
        }

        public void editprod(ProductList prodlist, int index)
        {
            DTbl.Rows[index][0] = prodlist.fID;
            DTbl.Rows[index][1] = prodlist.fProductName;
            DTbl.Rows[index][2] = prodlist.fProductQty;
            DTbl.Rows[index][3] = prodlist.fProductPrice;

            OdbcCommandBuilder OCB = new OdbcCommandBuilder(oda);
            oda.Update(DTbl);
        }

        public void adduser(UserList users)
        {
            DataRow DR = DTbl.NewRow();
            DR["fID"] = users.fID;
            DR["fUsername"] = users.fUsername;
            DR["fPassword"] = users.fPassword;
            DR["fNameofEmployee"] = users.fNameofEmployees;
            DR["fLevelofAccess"] = users.fLevelsOfAccess;

            DTbl.Rows.Add(DR);

            OdbcCommandBuilder OCB = new OdbcCommandBuilder(oda);
            oda.Update(DTbl);
        }

        public void edituser(UserList uslist, int index)
        {
            DTbl.Rows[index][0] = uslist.fID;
            DTbl.Rows[index][1] = uslist.fUsername;
            DTbl.Rows[index][2] = uslist.fPassword;
            DTbl.Rows[index][3] = uslist.fNameofEmployees;
            DTbl.Rows[index][4] = uslist.fLevelsOfAccess;

            OdbcCommandBuilder OCB = new OdbcCommandBuilder(oda);
            oda.Update(DTbl);
        }
        public void editQTY(upadteQTY upqty, int index)
        {
            DTbl.Rows[index]["fProductQty"] = upqty.Qty;


            OdbcCommandBuilder OCB = new OdbcCommandBuilder(oda);
            oda.Update(DTbl);
        }


        public void close()
        {
            oc.Close();
        }
    }
}